package com.levtech.moviemanager.db;

import java.util.ArrayList;

import com.levtech.moviemanager.contact.Contact;
import com.levtech.moviemanager.error.DatabaseException;
import com.levtech.moviemanager.loan.Loan;
import com.levtech.moviemanager.movie.Movie;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class DataStore
{
	protected static final String DB_NAME = "moviemanager.app.db";
	protected static final int DB_VERSION = 1;
	protected static final String MOVIE_TABLE = "Movie";
	protected static final String CONTACT_TABLE = "Contact";
	protected static final String LOAN_TABLE = "Loan";
	
	private SQLiteDatabase db;
	
	public DataStore(Context context)
	{
		this.db = (new DataInitializer(context)).getWritableDatabase();
	}
	
	public long insertMovie(Movie m)
	{
		SQLiteStatement stmnt = this.db.compileStatement("INSERT INTO " + MOVIE_TABLE + 
								" (title, description, genre, format, cast, studio) " +
								"values ('" + m.getTitle() + "','" + m.getDescription() + "','" +
								m.getGenre() + "','" + m.getFormat() + "','" + m.getCast() + "','" +
								m.getStudio() + "');");
		return stmnt.executeInsert();
	}
	
	public long insertContact(Contact c)
	{
		SQLiteStatement stmnt = this.db.compileStatement("INSERT INTO " + CONTACT_TABLE +
								" (firstName, lastName, phone) " +
								"values ('" + c.getFirstName() + "','" + c.getLastName() + "','" +
								c.getPhone() + "');");
		return stmnt.executeInsert();
	}
	
	public long insertLoan(Loan l)
	{
		int mID, cID;
		Cursor curr;
		
		// Extract movie_ID from table
		curr = this.db.rawQuery("SELECT movie_ID from " + MOVIE_TABLE + " WHERE title = '" +
								l.getMovie().getTitle() + "'", null);
		
		if(curr.moveToFirst())
			mID = curr.getInt(0);
		else
			throw new DatabaseException("movie not found during loan insertion");
		
		// Extract contact_ID from table
		curr = this.db.rawQuery("SELECT contact_ID from " + CONTACT_TABLE + " WHERE firstName = " +
								"'" + l.getContact().getFirstName() + "' AND lastName = '" +
								l.getContact().getLastName() + "'", null);
		
		if(curr.moveToFirst())
			cID = curr.getInt(0);
		else
			throw new DatabaseException("contact not found during loan insertion");
		
		SQLiteStatement stmnt = this.db.compileStatement("INSERT INTO " + LOAN_TABLE + 
								" (movie_ID, contact_ID, startDate, endDate, status) " +
								"values ('" + mID + "','" + cID + "','" + l.getStartDate() + 
								"','" + l.getEndDate() + "','" + l.getStatus() + "');");
		return stmnt.executeInsert();
	}
	
	public String[] selectTitle()
	{
		Cursor curr = this.db.rawQuery("SELECT title from " + MOVIE_TABLE, null);
		ArrayList<String> list = new ArrayList<String>();
		
		if(curr.moveToFirst())
		{
			do{
				list.add(curr.getString(0));
			}while(curr.moveToNext());
		}
		
		return list.toArray(new String[list.size()]);
	}
	
	public String[] selectName()
	{
		Cursor curr = this.db.rawQuery("SELECT firstName, lastName from " + CONTACT_TABLE, null);
		ArrayList<String> list = new ArrayList<String>();
		
		if(curr.moveToFirst())
		{
			do{
				list.add(curr.getString(1) + ", " + curr.getString(0));
			}while(curr.moveToNext());
		}
		
		return list.toArray(new String[list.size()]);
	}
	
	public Movie[] selectMovie()
	{
		Cursor curr = this.db.rawQuery("SELECT * from " + MOVIE_TABLE, null);
		ArrayList<Movie> list = new ArrayList<Movie>();
		
		if(curr.moveToFirst())
		{
			do{
				list.add(new Movie(curr.getString(1), curr.getString(2), curr.getString(3),
						curr.getString(4), curr.getString(5), curr.getString(6)));
			}while(curr.moveToNext());
		}
		
		return list.toArray(new Movie[list.size()]);
	}
	
	public Contact[] selectContact()
	{
		Cursor curr = this.db.rawQuery("SELECT * from " + CONTACT_TABLE, null);
		ArrayList<Contact> list = new ArrayList<Contact>();
		
		if(curr.moveToFirst())
		{
			do{
				list.add(new Contact(curr.getString(1), curr.getString(2), curr.getString(3)));
			}while(curr.moveToNext());
		}
		
		return list.toArray(new Contact[list.size()]);
	}
	
	public Loan[] selectLoan()
	{
		Cursor curr = this.db.rawQuery("SELECT * from " + LOAN_TABLE, null);
		ArrayList<Loan> list = new ArrayList<Loan>();
		
		if(curr.moveToFirst())
		{
			do{
				list.add(new Loan(selectMyMovie(curr.getString(1)),
						selectMyContact(curr.getString(2)),
						curr.getString(3), curr.getString(4), curr.getString(5)));
			}while(curr.moveToNext());
		}
		
		return list.toArray(new Loan[list.size()]);
	}
	
	private Movie selectMyMovie(String id)
	{
		Cursor curr = this.db.rawQuery("SELECT * from " + MOVIE_TABLE + " WHERE movie_id='" +
				id +"'", null);
		Movie m = null;
		
		if(curr.moveToFirst())
		{
			m = new Movie(curr.getString(1), curr.getString(2), curr.getString(3),
						curr.getString(4), curr.getString(5), curr.getString(6));
		}
		
		return m;
	}
	
	private Contact selectMyContact(String id)
	{
		Cursor curr = this.db.rawQuery("SELECT * from " + CONTACT_TABLE + " WHERE contact_id='" +
				id + "'", null);
		Contact c = null;
		
		if(curr.moveToFirst())
		{
			c = new Contact(curr.getString(1), curr.getString(2), curr.getString(3));
		}
		
		return c;
	}

}
